package cmp.database;

/*********************************************************
 * Contains the complete schema
 * Version additions are in Version, but also in here
 * XXX addDynamicColumns 
 */
/***
* CHAR: tinytext 1 byte, text 2 byte, mediumtext 2 byte
* max	256 char		65k char		16M char
* 		use VARCHAR for fields that need to be searched
* INT	tinyint 1 byte, smallint 2 byte,  mediumint 3 byte, int 4 byte, bigint 8 byte
* max	256               65k                16M				4394M		
* 		float 4 byte,  double 8 byte
*****/
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Vector;

import cmp.compile.CompileMain;
import cmp.compile.panels.CompilePanel;
import cmp.main.Globals;
import util.Debug.ErrorReport;
import assem.DBConn;

public class Schema {
	DBConn mDB;

	public Schema (DBConn targetConn, String Sver, String path) { // new database
		mDB = targetConn;
		try {
			loadSchema();
			
			String user = System.getProperty("user.name");
			mDB.executeUpdate(
			"insert into info (schemver, version, annoState, compiledate, username, path, summary) "
					+ " values(" 
					+ quote(Globals.DBver)			+ ","
					+ quote(Sver) 			+ "," 
					+ quote("start")			+ ","
					+ "NOW()" 				+ "," 
					+ quote(user) 			+ ", " 
					+ quote(path) 	+ "," 
					+ quote("START") + ")");
		} catch (Exception e) {
			ErrorReport.die(e, "entering schema version");
		}
	}

	public void loadSchema() {
		try {
			String sqlU;
			
			sqlU = "CREATE TABLE info ( " +
					"schemver 	tinytext, " +
					"version  	tinytext, " +
					"annoState 	tinytext, " +
					"compiledate date, " +
					"username 	tinytext, " +
					"path 		text, " + 
					"summary 	text," +  // for viewer
					"hasLib 		tinyint default 0," +
					"hasDE 		tinyint default 0," +
					// dynamic columns
					"allSeqLib 	text," +
					"allPOGLib 	text," +
					"allSeqDE 	text," +
					"allPOGDE 	text," +
					"allASM 		text," +
					"allMethods text," +
					"allTaxa 	text" +
					");";
			if (!mDB.hasTable("info")) mDB.executeUpdate(sqlU);
		   		
				// all PAVE assemblies
			sqlU =  "CREATE TABLE assembly ( " +
					"ASMid 		bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
					"ASMstr 		VARCHAR(30), " +
					"prefix		VARCHAR(10), " +
					"username 	tinytext, " +
					"nPep 		int default 0, " +		// number peptides
					"nUT 		int default 0, " +		// number unitrans
					"nAnnoUT 	int default 0, " +		// number annotated unitrans
					"nAlign 		int default 0, " +		// aligned sequences for all contigs
					"nExp  		bigint default 0, " +	// expression level count for all contigs
					"nLib 		smallint default 0, " +
					"nAnnoDB 	smallint default 0, " +
					"isPep		tinyint default 0," +
					"annoDBdate date, " +		// first one
					"annotationdate date, " +
					"assemblydate date, " +
					"remark 		text, " +	// CAS 3/29/13
					"index idx1(ASMid) " +
					");";
			if (!mDB.hasTable("assembly")) mDB.executeUpdate(sqlU);
				
				// all unitrans from all pave assemblies
			sqlU =  "CREATE TABLE unitrans ( " +
					"UTid bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
					"UTstr 		VARCHAR(30), " +
					"ASMid 		bigint, " +       	// assembly index
					"numAlign 	int default 0, " +				// PAVE num_clones
					"ntLen  		int default 0, " +		 		// PAVE consensus_bases
					"aaLen		int default 0, " +				// aaLen
					"HITid 		bigint default 0, " + // this is best anno hit
					"HITstr 		VARCHAR(30), " + 
					"e_value 	double, "     +		// since may not have db_unitrans_hits
					"totExp 		bigint, " +
					"totExpN 	bigint, " +
					"expList 	text, " +			// list of libraires
					"expListN 	text, " +			// list of normalized libraires
					"ntSeq 		mediumTEXT, " +  	// PAVE consensus
					"aaSeq 		mediumTEXT, " +		// from file
					"index 		idx1(UTid), " +
					"index 		idx2(UTstr)," +
					"index 		idx3 (HITid), " +	// CAS 11/9/12 
		   			"index 		idx4 (HITstr) " +
					");";
			if (!mDB.hasTable("unitrans")) mDB.executeUpdate(sqlU);
			
			sqlU =  "CREATE TABLE unitrans_hits ( " +
					"HITid 		bigint, "	 +		// index for unique hit
					"HITstr 		VARCHAR(30), " +
					"UTid 		bigint, " +       // index for unitrans
					"UTstr 		varchar(30), " + 
					"percent_id smallint, " +
					"alignment_len smallint, " +
					"seq_start 	smallint, " +
					"seq_end 	smallint, " +
					"hit_start smallint, " +
					"hit_end 	smallint, " +
					"e_value 	double, " +
					"bit_score 	float, " +
					"type		VARCHAR(20), " +
					"bestEval	tinyint default 0," +		// CAS 3/15/13 
					"bestAnno	tinyint default 0,"	+		// CAS 3/15/13
					"index idx1 (HITid), " +
					"index idx2 (HITstr), " +
					"index idx3 (UTstr), " +
					"index idx4 (UTid)" +
					");";
				if (!mDB.hasTable("unitrans_hits")) mDB.executeUpdate(sqlU);
				
			// DB hits from all assemblies (added first, so may have some not in unitrans_hits)
		 	sqlU =	"CREATE TABLE unique_hits ( " +
		   			"HITid 		BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
		   			"HITstr 		VARCHAR(30), " +
		   			"dbtype 		VARCHAR(10), " +
		   			"taxonomy 	VARCHAR(20), " +
		   			"isProtein 	tinyint, " +
		   			"description VARCHAR(250), " +
		   			"species 	VARCHAR(100), " +
		   			"length 		int, " +
		   			"sequence 	mediumtext, " +
		   			"go_list 	mediumtext, " +
		   			"index idx1 (HITid), " +
		   			"index idx2 (HITstr) " +
		   			");";
		 	if (!mDB.hasTable("unique_hits")) mDB.executeUpdate(sqlU);
		
			// type of POG (PAVE Orthologous Groups), e.g. orthoMCL
			sqlU =  "CREATE TABLE pog_method ( " +
					"PMid 			bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
					"PMstr 			VARCHAR(30), " +
					"PMtype			VARCHAR(30), " +
					"adddate date, " +
					"prefix			VARCHAR(3), " +
					"description 	text, " +
					"parameters		text, " + 		// not used yet, but should save
					"index 			idx1(PMid) " +
					");";
			if (!mDB.hasTable("pog_method")) mDB.executeUpdate(sqlU);
				
			// each method has one or more pogs
			sqlU =	"CREATE TABLE pog_groups ( " +
					"PGid bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
					"PGstr 		VARCHAR(30), " +
					"PMid 		bigint, " + 				// index into pog_method
					"count 		int, " +					// number of unitrans in pog
					"HITid 		bigint default 0, " +	// index into unique_hit, best hit across all members
					"HITstr 		VARCHAR(30), " +
					"e_value 	double, "     +	 		// best evalue
					"score 		tinyint, " +				// similarity of all hits for members
					"taxa		tinytext, " +
					"go_list		mediumtext, " +
					// dynamic summed counts for each PAVEdb is added 
					"index idx1(PGid), " +
					"index idx2(PGstr)," +
					"index idx3(HITid)," +
					"index idx4(HITstr)" +
					");";
			if (!mDB.hasTable("pog_members")) mDB.executeUpdate(sqlU);
		
			// each pog has two or more unitrans
			sqlU = 	"CREATE TABLE pog_members ( " +
					"MEMid 	bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
					"PGid  	bigint, " +   		// index into pog_groups
					"PGstr 	VARCHAR(30), " +
					"UTid  	bigint, " +			// index into unitrans
					"UTstr 	VARCHAR(30), " + 
					"index 	idx1(PGid)," +		
					"index 	idx2(PGstr)," +
					"index 	idx3(UTid)," + 		
					"index 	idx4(UTstr)" +
					");"; 
			mDB.executeUpdate(sqlU);
				
			// each POG has hits inherited by members
			sqlU = "CREATE TABLE pog_hits ( " +
					"PGid		bigint, " +				// index into POG_groups
					"PGstr		VARCHAR(30), " + 
					"HITid 		bigint, " +				// index into unique hits with more detail
					"HITstr 		VARCHAR(30), " +
					"e_value		double, " +				// best evalue
					"score 		tinyint, " +				// similarity of all hits for members
					"index 		idx1(PGid)," +		
					"index 		idx2(HITid)," +
					"index 		idx3(HITstr)" +
					");"; 
			if (!mDB.hasTable("pog_hits")) mDB.executeUpdate(sqlU);
		} 
		catch(Exception e) {
			ErrorReport.die(e, "Entering schema");
    		} 
	} 
	
	/**********************************************************
	 * XXX add dynamic columns
	 */
	static public boolean addDynamicColumns(DBConn mDB, CompilePanel cmpPanel) 
	{
		CompileMain.PrtDateMsg("\nAdd dynamic columns");
		HashMap<String,String> asm2pfx = new HashMap<String,String>();
		HashMap<String,HashSet<String>> asmLibs = new HashMap<String,HashSet<String>>();
		HashMap<String,HashSet<String>> asmDEs = new HashMap<String,HashSet<String>>();	
		Vector <String> allLibs = new Vector <String>();
		Vector <String> allDEs = new Vector <String>();
		boolean hasDE=true, hasRPKM=true;
		
		ResultSet rs;
		try
		{
	    		for(int x=0; x<cmpPanel.getAssemblyCount(); x++) 
	    		{
	    			String asm = cmpPanel.getAssemblyName(x);
				String prefix = cmpPanel.getSpeciesPanel().getPrefixForAssembly(asm);
				asm2pfx.put(asm, prefix);
				
				asmLibs.put(asm, new HashSet<String>());
				asmDEs.put(asm, new HashSet<String>());
				
				DBConn paveDBC = CompileMain.getDBConnection(cmpPanel, x);	
					
				int cntR=0, cntP=0;
				String r="", p="";
				rs = paveDBC.executeQuery("select libid from library where ctglib=0");
				while (rs.next())
				{
					String libName = rs.getString(1);
					asmLibs.get(asm).add(libName);
					addCheckCase(allLibs,libName);
					cntR++;  r += " " + libName;	
				}
				
				rs = paveDBC.executeQuery("show columns from contig");
				while (rs.next()) {
					String colName = rs.getString(1);
					if (colName.startsWith(Globals.PRE_S_DE)) {
						String col = colName.substring(Globals.PRE_S_DE.length());
						asmDEs.get(asm).add(col);
						addCheckCase(allDEs,col);
						cntP++; p += " " + col;
					}
				}
				CompileMain.PrtSpMsg(1, asm + " " + cntR + " RPKM (" + r + ") " + cntP + " DE (" + p + ")");		
	    		}
	    		if (allLibs.size() > 0) {
	    			CompileMain.PrtSpMsg(1, "Total unique library names: " + allLibs.size());
	    			String rl="";
	    			for (int i=0; i< allLibs.size(); i++) rl += " " + allLibs.get(i);
	    			CompileMain.PrtSpMsg(2, rl);	    			
	    		}
	    		else hasRPKM = false;
	    		
	    		if (allDEs.size() > 0) {
	    			CompileMain.PrtSpMsg(1, "Total unique DE names: " + allDEs.size());
	    			String pl="";
	    			for (int i=0; i< allDEs.size(); i++) pl += " " + allDEs.get(i);
	    			CompileMain.PrtSpMsg(2, pl);
	    		}
	    		else hasDE = false;
	    		
	    		if (!hasDE && !hasRPKM) {
	    			CompileMain.PrtSpMsg(1, "No RPKM or DE columns in any of the databases");
	    			return true;
	    		}
	    		
	    		// Add RPKM 
	    		for (String lib : allLibs)
	    		{
	    			mDB.executeUpdate("alter table unitrans add " + Globals.PRE_LIB + lib + " double default -2");
	    			mDB.executeUpdate("alter table pog_groups add " + Globals.PRE_LIBCNT + lib + " double default -2");
	    		}
	    		// Add DE 
	    		for (String de : allDEs)
	    		{	// CAS 9/27/13 - DEs can be between -1 and 1; generally -2 is not displayed (see sortTable.java)
	    			mDB.executeUpdate("alter table unitrans add " + Globals.PRE_DE + de + " double default -2");
	    			mDB.executeUpdate("alter table pog_groups add " + Globals.PRE_DECNT + de + " double default -2");
	    		}

	    		return true;
		}
		catch(Exception e) {
			ErrorReport.prtReport(e, "Error adding library count columns");
			return false;
		}
		catch(Error e) {
			ErrorReport.reportFatalError(e, "Error adding library count columns", null);
			return false;
		}
	}
	static private void addCheckCase(Vector <String> set, String name)
	{
		boolean found = false;
		for (String s : set)
		{
			if (s.equalsIgnoreCase(name)) 
			{
				found = true;
				break;
			}
		}
		if (!found) set.add(name);
	}
	
	private String quote(String word) {
		return "'" + word + "'"; 
	}	
}
